USE [WarehouseManagement]
GO

/****** Object:  StoredProcedure [dbo].[GetCollectionOrder]    Script Date: 04/20/2011 10:27:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		duylv00360
-- Create date: 3/19/2011
-- Description:	This stored procedure use to get all order
-- =============================================
ALTER PROCEDURE [dbo].[GetCollectionOrder] 
	-- Add the parameters for the stored procedure here
@IsShowAll int = 1 ,
@IsMaterial int = 2
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF @IsMaterial = 1
	BEGIN
		SELECT	
				[Order].OrderID,
				[Order].OrderCode,
				[Order].Date as OrderDate,
				[Order].EmployeeAID, 
				E.FullName EmployeeAName,
				[Order].ContactID, 
				c.FullName ContactName,
				[Order].EmployeeBID,
				em.FullName EmployeeBName, 
				[Order].IsImport,
				[Order].IsDelete
		FROM	[Order]
		LEFT JOIN	Employee E
		ON		[Order].EmployeeAID = E.EmployeeID 
		LEFT JOIN	Employee em
		ON		[Order].EmployeeBID = em.EmployeeID 
		LEFT JOIN	Contact c
		ON		[Order].ContactID = c.ContactID		
		WHERE	[Order].OrderCode LIKE 'M%'
				AND
				[Order].IsDelete = 0 OR [Order].IsDelete  = @IsShowAll
		ORDER BY [Order].Date DESC
	END
	ELSE
	IF @IsMaterial = 0
	BEGIN
		SELECT	
				[Order].OrderID,
				[Order].OrderCode,
				[Order].Date as OrderDate,
				[Order].EmployeeAID, 
				E.FullName EmployeeAName,
				[Order].ContactID,
				c.FullName ContactName, 
				[Order].EmployeeBID,
				em.FullName EmployeeBName, 
				[Order].IsImport,
				[Order].IsDelete
		FROM	[Order]
		LEFT JOIN	Employee E
		ON		[Order].EmployeeAID = E.EmployeeID 
		LEFT JOIN	Employee em
		ON		[Order].EmployeeBID = em.EmployeeID 
		LEFT JOIN	Contact c
		ON		[Order].ContactID = c.ContactID	
		WHERE	[Order].OrderCode LIKE 'P%'
				AND
				[Order].IsDelete = 0 OR [Order].IsDelete  = @IsShowAll
		ORDER BY [Order].Date DESC
	END
	ELSE
	BEGIN
		SELECT	
				[Order].OrderID,
				[Order].OrderCode,
				[Order].Date as OrderDate,
				[Order].EmployeeAID, 
				E.FullName EmployeeAName,
				[Order].ContactID, 
				c.FullName ContactName,
				[Order].EmployeeBID,
				em.FullName EmployeeBName, 
				[Order].IsImport,
				[Order].IsDelete
		FROM	[Order]
		LEFT JOIN	Employee E
		ON		[Order].EmployeeAID = E.EmployeeID 
		LEFT JOIN	Employee em
		ON		[Order].EmployeeBID = em.EmployeeID 
		LEFT JOIN	Contact c
		ON		[Order].ContactID = c.ContactID	
		WHERE	[Order].IsDelete = 0 --OR [Order].IsDelete  = @IsShowAll
		ORDER BY [Order].Date DESC
	END	
END

GO
/*
exec GetCollectionOrder @isshowall = 0
*/